package com.suitsoft.bdms.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.suitsoft.bdms.service.ServiceException;

/**
 * Excel工具类.
 * 
 * @author coombe
 */
public class ExcelTools {
	/** 列A */
	public static final int COL_A = 0;
	/** 列B */
	public static final int COL_B = 1;
	/** 列C */
	public static final int COL_C = 2;
	/** 列D */
	public static final int COL_D = 3;
	/** 列E */
	public static final int COL_E = 4;
	/** 列F */
	public static final int COL_F = 5;
	/** 列G */
	public static final int COL_G = 6;
	/** 列H */
	public static final int COL_H = 7;
	/** 列I */
	public static final int COL_I = 8;
	/** 列J */
	public static final int COL_J = 9;
	/** 列K */
	public static final int COL_K = 10;
	/** 列L */
	public static final int COL_L = 11;
	/** 列M */
	public static final int COL_M = 12;
	/** 列N */
	public static final int COL_N = 13;
	/** 列O */
	public static final int COL_O = 14;
	/** 列P */
	public static final int COL_P = 15;
	/** 列Q */
	public static final int COL_Q = 16;
	/** 列R */
	public static final int COL_R = 17;
	/** 列S */
	public static final int COL_S = 18;
	/** 列T */
	public static final int COL_T = 19;
	/** 列U */
	public static final int COL_U = 20;
	/** 列V */
	public static final int COL_V = 21;
	/** 列W */
	public static final int COL_W = 22;
	/** 列X */
	public static final int COL_X = 23;
	/** 列Y */
	public static final int COL_Y = 24;
	/** 列Z */
	public static final int COL_Z = 25;
	
	/** 日志输出对象 */
	protected static final Logger LOGGER = LoggerFactory.getLogger(ExcelTools.class);
	
	private ExcelTools() {
		
	}
	
	/**
	 * 打开Excel工作簿.
	 */
	public static Workbook openExcelWorkbook(String fileName) {
		Workbook result = null;

		File inputFile = null;
		
		try {
			//避免乱码的设置
			WorkbookSettings setting = new WorkbookSettings();
			java.util.Locale locale = new java.util.Locale("zh", "CN");

			setting.setLocale(locale);
			setting.setEncoding("ISO-8859-1");
			
			inputFile = new File(fileName);
			
			if (!inputFile.exists()) {
				LOGGER.error("打开Excel文件失败:[" + inputFile.getPath() + "]文件不存在!");
				throw new RuntimeException("打开Excel文件失败:[" + inputFile.getPath() + "]文件不存在!");
			}
			
			result = Workbook.getWorkbook(inputFile, setting);
		} catch (BiffException e) {
			LOGGER.error(e.getMessage());
			throw new ServiceException("打开Excel文件失败，请检查文件格式是否正确！");
		} catch (IOException e) {
			LOGGER.error(e.getMessage());
			throw new ServiceException("读取Excel文件失败，请重试！");
		}

		return result;
	}

	/**
	 * 关闭Excel工作簿. 
	 */
	public static void closeExcelWorkbook(Workbook workbook) {
		if (workbook != null) { 
			workbook.close();
		}
	}
	
	public static ArrayList<String> readExcel(File inputFile, int inputFileSheetIndex) throws Exception {
		ArrayList<String> list = new ArrayList<String>();
		Workbook book = null;
		Cell cell = null;

		//避免乱码的设置
		WorkbookSettings setting = new WorkbookSettings();
		java.util.Locale locale = new java.util.Locale("zh", "CN");

		setting.setLocale(locale);
		setting.setEncoding("ISO-8859-1");
		book = Workbook.getWorkbook(inputFile, setting);

		Sheet sheet = book.getSheet(inputFileSheetIndex);
		for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {// Excel第一行为表头,因此J初值设为1
			for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {// 只需从Excel中取出2列
				cell = sheet.getCell(colIndex, rowIndex);
				list.add(cell.getContents());
			}
		}

		book.close();

		return list;
	}

}
